package com.tidu.utils;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * 通用 DbUtils 模板工具类
 */
public class DbUtilsTemplate {

    private final QueryRunner queryRunner;

    public DbUtilsTemplate(DataSource dataSource) {
        this.queryRunner = new QueryRunner(dataSource);
    }

    /**
     * 查询 Java Bean 列表（泛型支持）
     */
    public <T> List<T> queryForList(String sql, Class<T> clazz, Object... params) throws SQLException {
        return queryRunner.query(sql, new BeanListHandler<>(clazz), params);
    }

    /**
     * 查询 Map 列表（适合动态表结构）
     */
    public List<Map<String, Object>> queryForMapList(String sql, Object... params) throws SQLException {
        return queryRunner.query(sql, new MapListHandler(), params);
    }

    /**
     * 查询单个 Java Bean
     */
    public <T> T queryForObject(String sql, Class<T> clazz, Object... params) throws SQLException {
        List<T> list = queryRunner.query(sql, new BeanListHandler<>(clazz), params);
        return list != null && !list.isEmpty() ? list.get(0) : null;
    }

    /**
     * 查询单个Map记录
     */
    public Map<String, Object> queryForMap(String sql, Object... params) throws SQLException {
        List<Map<String, Object>> list = queryRunner.query(sql, new MapListHandler(), params);
        return list != null && !list.isEmpty() ? list.get(0) : null;
    }

    /**
     * 查询单个字段值（如 COUNT）
     */
    public <T> T queryForScalar(String sql, Class<T> clazz, Object... params) throws SQLException {
        return queryRunner.query(sql, new ScalarHandler<T>(), params);
    }

    /**
     * 获取总记录数
     */
    public long count(String sql, Object... params) throws SQLException {
        return queryRunner.query(sql, new ScalarHandler<>(), params);
    }

    /**
     * 执行更新（插入、更新、删除）
     */
    public int update(String sql, Object... params) throws SQLException {
        return queryRunner.update(sql, params);
    }

    /**
     * 获取连接
     */
    public Connection getConnection() throws SQLException {
        return queryRunner.getDataSource().getConnection();
    }

    /**
     * 批量插入（使用真正的 JDBC 批处理）
     */
    public void batchInsert(String sql, List<Map<String, Object>> dataList) throws SQLException {
        if (dataList == null || dataList.isEmpty()) return;

        try (Connection conn = queryRunner.getDataSource().getConnection(); PreparedStatement ps = conn.prepareStatement(sql)) {

            conn.setAutoCommit(false); // 关闭自动提交

            // 获取字段顺序
            String[] columns = dataList.get(0).keySet().toArray(new String[0]);

            for (Map<String, Object> map : dataList) {
                for (int i = 0; i < columns.length; i++) {
                    ps.setObject(i + 1, map.get(columns[i]));
                }
                ps.addBatch();
            }

            ps.executeBatch(); // 一次性提交所有插入
            conn.commit();     // 提交事务

        } catch (SQLException e) {
            // 可选：回滚
            throw e;
        }
    }


    /**
     * 开启事务（手动提交）
     */
    public Connection startTransaction() throws SQLException {
        Connection conn = queryRunner.getDataSource().getConnection();
        conn.setAutoCommit(false);
        return conn;
    }

    /**
     * 提交事务
     */
    public void commit(Connection conn) throws SQLException {
        if (conn != null && !conn.isClosed() && !conn.getAutoCommit()) {
            conn.commit();
        }
    }

    /**
     * 回滚事务
     */
    public void rollback(Connection conn) {
        if (conn != null) {
            try {
                conn.rollback();
            } catch (SQLException e) {
                // ignore
            }
        }
    }

    /**
     * 关闭连接
     */
    public void close(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                // ignore
            }
        }
    }
}
